from flask import Blueprint, request, jsonify
import logging
from datetime import datetime
from app import db
from app.models.mainTable import MainTable
from app.models.table import Table
from app.models.table2 import Table2
from app.models.exponent import Exponent
from app.models.event import Event
# 导入需要的模型和函数
from sqlalchemy import or_, and_
from sqlalchemy import func

from app.Tools.climb_parties import crawl_match_data
from app.Tools.climb_exponent import fetch_exponent_data
from app.Tools.climb_jinqiushu import fetch_match_data
from app.Tools.climb_yarang import fetch_match_data_concurrent
from app.Tools.climb_pid import get_first_parts_from_A_arrays_v2_optimized
from app.Tools.climd_time_pid import wanzhengbifen_main

import time


# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

mainTables_bp = Blueprint('mainTable', __name__, url_prefix='/mainTable')

# 获取总计的数据接口
@mainTables_bp.route('/count', methods=['GET'])
def get_main_table_count():
    cid = request.args.get('cid')
    pid = request.args.get('pid')

    # 查询字段t等于1的总条数
    chang_count = MainTable.query.filter(MainTable.t == "1",MainTable.pid == pid,MainTable.cid == cid).count()
    main_count = MainTable.query.filter(MainTable.t == "1" , MainTable.result == "2",MainTable.pid == pid,MainTable.cid == cid).count()
    ping_count = MainTable.query.filter(MainTable.t == "1" , MainTable.result == "1",MainTable.pid == pid,MainTable.cid == cid).count()
    ke_count = MainTable.query.filter(MainTable.t == "1" , MainTable.result == "0",MainTable.pid == pid,MainTable.cid == cid).count()

    chang2_count = MainTable.query.filter(MainTable.t == "2",MainTable.pid == pid,MainTable.cid == cid).count()
    main2_count = MainTable.query.filter(MainTable.t == "2" , MainTable.result == "2",MainTable.pid == pid,MainTable.cid == cid).count()
    ping2_count = MainTable.query.filter(MainTable.t == "2" , MainTable.result == "1",MainTable.pid == pid,MainTable.cid == cid).count()
    ke2_count = MainTable.query.filter(MainTable.t == "2" , MainTable.result == "0",MainTable.pid == pid,MainTable.cid == cid).count()
    return jsonify({
        'chang_count': chang_count,
        'main_count': main_count,
        'ping_count': ping_count,
        'ke_count': ke_count,
        'chang2_count': chang2_count,
        'main2_count': main2_count,
        'ping2_count': ping2_count,
        'ke2_count': ke2_count
        })


# 筛选查询接口
@mainTables_bp.route('/search', methods=['POST'])
def search_main_tables():
    try:
        # 获取前端传来的筛选条件和分页参数
        search_data = request.get_json() or {}
        
        # 从JSON数据中获取分页参数
        page = search_data.get('page', 1)
        per_page = search_data.get('per_page', 20)
        
        # 参数验证
        if page < 1:
            page = 1
        if per_page < 1 or per_page > 100:
            per_page = 20
            
        # 构建查询条件
        query = MainTable.query
        
        # 处理查询条件（排除分页参数）
        # 收集所有条件
        all_conditions = []

        # 添加pid条件
        pid_value = search_data.get('pid')
        if pid_value:
            all_conditions.append(MainTable.pid == pid_value)

        # 添加cid条件
        cid_value = search_data.get('cid')
        if cid_value:
            all_conditions.append(MainTable.cid == cid_value)

        # 添加t条件
        t_value = search_data.get('t')
        if t_value:
            all_conditions.append(MainTable.t == t_value)


        # 添加result条件
        result_value = search_data.get('result')
        if result_value:
            all_conditions.append(MainTable.result == result_value)

        
        # 终场条件end_half
        score_value = search_data.get('score')
        if score_value:
            all_conditions.append(MainTable.end_half == score_value)
        
        # 早盘条件
        handicap_type = search_data.get('morning_session')
        if handicap_type:
            handicap_condition = and_(
                MainTable.morning_seesion.like(f"%{handicap_type}%"),
                ~MainTable.morning_seesion.like(f"%受让{handicap_type}%"),
                ~MainTable.morning_seesion.like(f"%{handicap_type}/%"),
                ~MainTable.morning_seesion.like(f"%/{handicap_type}%")
            )
            all_conditions.append(handicap_condition)

        # 临场盘条件
        closing_session_type = search_data.get('closing_session')
        if closing_session_type:
            closing_session_condition = and_(
                MainTable.closing_session.like(f"%{closing_session_type}%"),
                ~MainTable.closing_session.like(f"%受让{closing_session_type}%"),
                ~MainTable.closing_session.like(f"%{closing_session_type}/%"),
                ~MainTable.closing_session.like(f"%/{closing_session_type}%")
            )
            all_conditions.append(closing_session_condition)

        # 中场盘条件
        midfield_type = search_data.get('midfield')
        if midfield_type:
            midfield_condition = and_(
                MainTable.midfield.like(f"%{midfield_type}%"),
                ~MainTable.midfield.like(f"%受让{midfield_type}%"),
                ~MainTable.midfield.like(f"%{midfield_type}/%"),
                ~MainTable.midfield.like(f"%/{midfield_type}%")
            )
            all_conditions.append(midfield_condition)
        
        # 大小球筛选 (严格精确匹配，增加格式处理)
        big_small_value = search_data.get('big_small_plate')
        if big_small_value is not None and str(big_small_value).strip() != "":
            # 统一转换为字符串并去除首尾空格
            normalized_value = str(big_small_value).strip()
            
            # 计算字段中的空格数量为2
            space_count_condition = (
                func.length(MainTable.big_small_plate) - 
                func.length(func.replace(MainTable.big_small_plate, ' ', '')) == 2
            )
            
            # 提取第一个空格后的子字符串
            substr_after_first_space = func.substr(
                MainTable.big_small_plate,
                func.instr(MainTable.big_small_plate, ' ') + 1
            )
            
            # 提取第二个空格前的内容并与目标值比较
            second_space_pos = func.instr(substr_after_first_space, ' ')
            second_segment = func.substr(substr_after_first_space, 1, second_space_pos - 1)
            second_segment_condition = (second_segment == normalized_value)
            
            # 将两个条件都添加到条件列表
            all_conditions.append(space_count_condition)
            all_conditions.append(second_segment_condition)

        # 中场大小球筛选 (同样处理)
        mid_big_small_value = search_data.get('mid_big_small_plate')
        if mid_big_small_value is not None and str(mid_big_small_value).strip() != "":
            normalized_mid_value = str(mid_big_small_value).strip()
            
            # 条件1：字段中包含2个空格
            mid_space_count_condition = (
                func.length(MainTable.mid_big_small_plate) - 
                func.length(func.replace(MainTable.mid_big_small_plate, ' ', '')) == 2
            )
            
            # 提取第一个空格后的子字符串
            mid_substr_after_first_space = func.substr(
                MainTable.mid_big_small_plate,
                func.instr(MainTable.mid_big_small_plate, ' ') + 1
            )
            
            # 条件2：第二个空格前的内容等于normalized_mid_value
            mid_second_space_pos = func.instr(mid_substr_after_first_space, ' ')
            mid_second_segment = func.substr(mid_substr_after_first_space, 1, mid_second_space_pos - 1)
            mid_second_segment_condition = (mid_second_segment == normalized_mid_value)
            
            # 将两个条件添加到条件列表
            all_conditions.append(mid_space_count_condition)
            all_conditions.append(mid_second_segment_condition)
        
        # 应用所有条件 (所有条件之间是AND关系)
        if all_conditions:
            query = query.filter(and_(*all_conditions))
        
        # 执行分页查询
        pagination = query.paginate(
            page=page,
            per_page=per_page,
            error_out=False
        )
        
        # 构造返回数据
        result_data = [main_table.to_dict() for main_table in pagination.items]
        
        # 返回筛选结果
        return jsonify({
            'code': 200,
            'data': result_data,
            'total': pagination.total,
            'page': page,
            'per_page': per_page,
            'pages': pagination.pages
        }), 200
        
    except Exception as e:
        logger.error(f"筛选查询失败: {str(e)}")
        return jsonify({
            'code': 500,
            'message': f'筛选查询失败: {str(e)}'
        }), 500


# 爬取赛事数据
@mainTables_bp.route('/event', methods=['POST'])
def add_event():
    start_time = time.time()
    try:
        today_date = datetime.today().strftime('%Y%m%d')
        # 获取query参数
        query_params = request.get_json() or {}
        
        # 如果前端有传date参数，则调用wanzhengbifen_main函数获取数据
        if query_params.get('date') != today_date:
            logger.info(f"开始爬取完场赛事数据，日期：{query_params.get('date')}")
            event_data = wanzhengbifen_main(query_params.get('date'))
        else:
            logger.info("开始爬取赛事数据")
            event_data = get_first_parts_from_A_arrays_v2_optimized(today_date)

        logger.info(f"成功获取比赛球队数据，共 {len(event_data)} 条记录")
        
        # 使用 merge 方法实现 upsert 操作
        merged_count = 0
        for p in event_data:
            # 确保 pid_time 是 date 对象
            pid_time_value = p.get('pidtime')
            if isinstance(pid_time_value, str):
                pid_time_obj = datetime.strptime(pid_time_value, '%Y-%m-%d').date()
            else:
                pid_time_obj = pid_time_value
                
            event = Event(
                pid=p['pid'],
                tname1=p['home_team'],
                tname2=p['away_team'],
                league=p['league'],
                pidtime=pid_time_obj
            )
            
            # 使用 merge 实现 upsert 操作
            db.session.merge(event)
            merged_count += 1
        
        db.session.commit()
        end_time = time.time()
        execution_time = end_time - start_time
        logger.info(f"爬取赛事接口执行完成，总耗时: {execution_time:.2f}秒")
        
        # 添加返回语句
        return jsonify({
            'message': '赛事数据更新成功',
            'data_count': merged_count,
            'execution_time': f"{execution_time:.2f}秒"
        }), 200

    except Exception as e:
        db.session.rollback()
        logger.error(f"插入event表时出错: {str(e)}", exc_info=True)
        # 添加错误返回语句
        return jsonify({
            'error': '赛事数据更新失败',
            'message': str(e)
        }), 500

# 爬虫取指定的pid和cid和t的所有数据
@mainTables_bp.route('/climbpcid', methods=['POST'])
def select_pid_add():
    
    start_time = time.time()  # 记录开始时间
    data = request.get_json()
    pid = data.get('pid')
    cid = data.get('cid')
    t1 = data.get('t1')
    t2 = data.get('t2')
    score = "("+data.get('firstscore')+")"
    
    processed_count = 0  # 初始化计数器
    
    # 初始化变量避免未定义错误
    match_data = []
    match_data2 = []

    try:
        main_table_objects = []
        table_objects = []
        table2_objects = []
        
        # 判断t1和t2的值是否为空
        if t1 and t2:
            match_data = crawl_match_data(1,pid, cid)
            logger.info(f"获取t1比赛数据 {len(match_data)} 条")
            match_data2 = crawl_match_data(2,pid, cid)
            logger.info(f"获取t2比赛数据 {len(match_data2)} 条")
        else:
            if t1:
                match_data = crawl_match_data("1",pid, cid)
                logger.info(f"获取t1比赛数据 {len(match_data)} 条")
                if len(match_data) == 0:
                    return jsonify({
                    'status': 'success',
                    'message': '该比赛数据为空'
                    }), 200
            if t2:
                match_data2 = crawl_match_data("2",pid, cid)
                logger.info(f"获取t2比赛数据 {len(match_data2)} 条")
                if len(match_data2) == 0:
                    return jsonify({
                    'status': 'success',
                    'message': '该比赛数据为空'
                    }), 200


        start_time1 = time.time()
        exponent_data = fetch_exponent_data(pid, cid)
        logger.info(f"获取指数数据 {len(exponent_data)} 条")

        # 确保 exponent_data 包含足够的数据
        if exponent_data and len(exponent_data) >= 6:
            try:
                # 创建 Exponent 模型实例
                exponent_entry = Exponent(
                    pid=pid,
                    cid=cid,
                    mteam=exponent_data[0] if len(exponent_data) > 0 else None,
                    dopen=exponent_data[1] if len(exponent_data) > 1 else None,
                    vteam=exponent_data[2] if len(exponent_data) > 2 else None,
                    mteam2=exponent_data[3] if len(exponent_data) > 3 else None,
                    dopen2=exponent_data[4] if len(exponent_data) > 4 else None,
                    vteam2=exponent_data[5] if len(exponent_data) > 5 else None
                )
                
                # 使用 merge 实现 upsert 操作
                db.session.merge(exponent_entry)
                logger.info("指数数据插入/更新完成")
                db.session.commit()
                end_time1 = time.time()
                execution_time1 = end_time1 - start_time1
                logger.info(f"指数接口执行完成，总耗时: {execution_time1:.2f}秒")
            except Exception as e:
                logger.error(f"指数数据插入失败: {str(e)}")
                db.session.rollback()


        # 处理match_data部分
        if len(match_data) > 0:
            for match in match_data:
                try:
                    mid = match['mid']
                    parties = match['parties']
                    end_half = match['end_half']
                    first_half = match['first_half']
                    result = match['result']
                    midtime = match['midtime']
                    league = match['league']
                    
                    logger.info(f"处理比赛数据: MID={mid}")

                    if score != first_half:
                        logger.info(f"比赛的上半场比分不符合条件: MID={mid}")
                        continue

                    # 修改这里：将参数改为字符串形式的元组列表
                    yarang_list = fetch_match_data_concurrent(mid, cid)

                    if yarang_list is None:
                        continue
                    
                    print("亚让传输成功")

                    # 遍历去存到数据库
                    jinqiu_list = fetch_match_data(mid, cid)

                    if jinqiu_list is None:
                        continue

                    print("进球数传输成功")
                    goal_time = yarang_list[0]
                    morning_seesion = yarang_list[1]
                    closing_seesion = yarang_list[2]
                    midfield = yarang_list[3]
                    table_content = yarang_list[4]

                    big_small_plate = jinqiu_list[0]
                    mid_big_small_plate = jinqiu_list[1]
                    jinqiushu_table = jinqiu_list[2]
                    print("遍历成功")

                    # 收集主表对象用于批量插入
                    main_table_objects.append(MainTable(
                        mid=mid, 
                        cid=cid, 
                        pid=pid,
                        league = league,
                        parties=parties,
                        first_half=first_half,
                        end_half=end_half, 
                        goal_time=goal_time, 
                        morning_seesion=morning_seesion,
                        closing_session=closing_seesion,
                        midfield=midfield,
                        big_small_plate=big_small_plate,
                        mid_big_small_plate=mid_big_small_plate,
                        result=result,
                        midtime=midtime,
                        t=1))
                        
                    # 收集详情表对象用于批量插入
                    table_objects.append(Table(mid=mid, table_content=table_content))
                    table2_objects.append(Table2(mid=mid, jinqiushu_table=jinqiushu_table))
                    
                    processed_count += 1
                except IndexError as e:
                    logger.error(f"列表索引越界错误: {str(e)}, MID={mid}, CID={cid}")
                    continue 
                except Exception as match_error:
                    logger.error(f"处理比赛数据时出错 (MID={match.get('mid', 'unknown')}): {str(match_error)}")
                    continue

        # 处理match_data2部分
        if len(match_data2) > 0:
            for match in match_data2:
                try:
                    mid = match['mid']
                    parties = match['parties']
                    end_half = match['end_half']
                    first_half = match['first_half']
                    result = match['result']
                    midtime = match['midtime']
                    league = match['league']
                    
                    logger.info(f"处理比赛数据: MID={mid}")

                    if score != first_half:
                        logger.info(f"比赛的上半场比分不符合条件: MID={mid}")
                        continue

                    # 修改这里：将参数改为字符串形式的元组列表
                    yarang_list = fetch_match_data_concurrent(mid, cid)

                    if yarang_list is None:
                        continue
                    
                    print("亚让传输成功")

                    # 遍历去存到数据库
                    jinqiu_list = fetch_match_data(mid, cid)

                    if jinqiu_list is None:
                        continue

                    print("进球数传输成功")
                    goal_time = yarang_list[0]
                    morning_seesion = yarang_list[1]
                    closing_seesion = yarang_list[2]
                    midfield = yarang_list[3]
                    table_content = yarang_list[4]

                    big_small_plate = jinqiu_list[0]
                    mid_big_small_plate = jinqiu_list[1]
                    jinqiushu_table = jinqiu_list[2]
                    print("遍历成功")

                    # 收集主表对象用于批量插入
                    main_table_objects.append(MainTable(
                        mid=mid, 
                        cid=cid, 
                        pid=pid,
                        league = league,
                        parties=parties,
                        first_half=first_half,
                        end_half=end_half, 
                        goal_time=goal_time, 
                        morning_seesion=morning_seesion,
                        closing_session=closing_seesion,
                        midfield=midfield,
                        big_small_plate=big_small_plate,
                        mid_big_small_plate=mid_big_small_plate,
                        result=result,
                        midtime=midtime,
                        t=2))
                        
                    # 收集详情表对象用于批量插入
                    table_objects.append(Table(mid=mid, table_content=table_content))
                    table2_objects.append(Table2(mid=mid, jinqiushu_table=jinqiushu_table))
                    
                    processed_count += 1
                except IndexError as e:
                    logger.error(f"列表索引越界错误: {str(e)}, MID={mid}, CID={cid}")
                    continue 
                except Exception as match_error:
                    logger.error(f"处理比赛数据时出错 (MID={match.get('mid', 'unknown')}): {str(match_error)}")
                    continue
     

        # 批量插入主表数据
        if main_table_objects:
            db.session.bulk_save_objects(main_table_objects)
            logger.info(f"批量插入 {len(main_table_objects)} 条主表数据")
        
        # 批量插入亚让表数据
        if table_objects:
            db.session.bulk_save_objects(table_objects)
            logger.info(f"批量插入 {len(table_objects)} 条亚让表数据")

        # 批量插入进球数表数据
        if table2_objects:
            db.session.bulk_save_objects(table2_objects)
            logger.info(f"批量插入 {len(table2_objects)} 条详进球数表数据")
        
        # 一次性提交所有更改
        db.session.commit()
        logger.info(f"成功提交所有数据到数据库")
        
        end_time = time.time()  # 记录结束时间
        execution_time = end_time - start_time  # 计算执行时间
        
        logger.info(f"接口执行完成，总耗时: {execution_time:.2f}秒，共处理 {processed_count} 条记录")
        
        # 返回执行时间和处理记录数
        return jsonify({
            'status': 'success',
            'message': f'数据爬取并存储成功，共处理 {processed_count} 条记录',
            'processed_count': processed_count,
            'execution_time': f'{execution_time:.2f}秒'
        }), 200
        
    except Exception as e:
        end_time = time.time()  # 记录结束时间
        execution_time = end_time - start_time  # 计算执行时间
        
        logger.error(f"接口执行失败，耗时: {execution_time:.2f}秒，错误: {str(e)}")
        db.session.rollback()
        return jsonify({
            'status': 'error',
            'message': f'数据爬取失败: {str(e)}',
            'execution_time': f'{execution_time:.2f}秒'
        }), 500